<?php
class Bagi_subsidi_non_syahriyah extends CI_Model 
{

	function __construct()
	{
		parent::__construct();
	}

	function jenjang()
	{
		$this->db->select('distinct(a_non_sy.id_m_j_pendidikan), j_pendidikan');
		$this->db->from('a_non_sy');
		$this->db->join('m_j_pendidikan', 'm_j_pendidikan.id_m_j_pendidikan = a_non_sy.id_m_j_pendidikan');
		$this->db->order_by('a_non_sy.id_m_j_pendidikan');

		$query = $this->db->get();		
		return $query->result();
	}

	function tagihan($id_m_j_pendidikan)
	{
		$this->db->select('*,(
			SELECT COUNT(id_b_sub_non_sy) 
			FROM b_sub_non_sy 
			JOIN b_non_sy ON b_non_sy.id_b_non_sy = b_sub_non_sy.id_b_non_sy
			where id_a_non_sy = a_non_sy.id_a_non_sy
		) as jml_tersubsidi,(
			SELECT COUNT(id_m_tholabah) 
			FROM b_non_sy 
			WHERE id_a_non_sy = a_non_sy.id_a_non_sy
		) as jml_tertagih');
		$this->db->from('a_non_sy');
		$this->db->where('id_m_j_pendidikan',$id_m_j_pendidikan);
		$this->db->where('id_a_non_sy IN (SELECT id_a_non_sy FROM b_non_sy)');
		$this->db->order_by('akhir_bayar');

		$query = $this->db->get();		
		return $query->result();
	}

	function tholabah($id_a_non_sy )
	{
		$this->db->select('b_sub_non_sy.id_b_sub_non_sy, b_non_sy.id_m_tholabah, 
			nama_lengkap, kelas, jk, nama_subsidi, besar_subsidi, (besar_bayar-besar_subsidi) as sisa');
		$this->db->from('b_sub_non_sy');
		$this->db->join('a_subsidi','a_subsidi.id_a_subsidi = b_sub_non_sy.id_a_subsidi');
		$this->db->join('b_non_sy','b_non_sy.id_b_non_sy = b_sub_non_sy.id_b_non_sy');
		$this->db->join('a_non_sy','a_non_sy.id_a_non_sy = b_non_sy.id_a_non_sy');
		$this->db->join('m_tholabah','m_tholabah.id_m_tholabah = b_non_sy.id_m_tholabah');
		$this->db->join('b_kelas','b_kelas.id_m_tholabah = m_tholabah.id_m_tholabah');
		$this->db->join('a_kelas','a_kelas.id_a_kelas = b_kelas.id_a_kelas');
		$this->db->join('m_kelas','m_kelas.id_m_kelas = a_kelas.id_m_kelas');
		$this->db->where('b_non_sy.id_a_non_sy',$id_a_non_sy );
		$this->db->where('a_kelas.id_m_th_ajaran',id_ta_aktif() ); // Untuk mengetahui tholabah sekarang kelas berapa, NAH kalo belum dibagi kelas gimana?
		$this->db->order_by('nama_lengkap');

		$query = $this->db->get();		
		return $query->result();
	}

	function subsidi()
	{
		$this->db->select('
			id_a_subsidi, nama_subsidi, total_subsidi,
			(select COALESCE(sum(besar_subsidi),0) from b_sub_sy where id_a_subsidi = a_subsidi.id_a_subsidi) as dipakai_sub_sy,
			(select COALESCE(sum(besar_subsidi),0) from b_sub_non_sy where id_a_subsidi = a_subsidi.id_a_subsidi) as dipakai_sub_non_sy,
			(total_subsidi-(select COALESCE(sum(besar_subsidi),0) from b_sub_sy where id_a_subsidi = a_subsidi.id_a_subsidi)-(select COALESCE(sum(besar_subsidi),0) from b_sub_non_sy where id_a_subsidi = a_subsidi.id_a_subsidi)) as sisa
			', false);
		$this->db->from('a_subsidi');
		$this->db->having('sisa >=1');

		$query = $this->db->get();		
		return $query->result();
	}

	function sisa_subsidi($id_a_subsidi)
	{
		$this->db->select('
			id_a_subsidi, nama_subsidi, total_subsidi,
			(select COALESCE(sum(besar_subsidi),0) from b_sub_sy where id_a_subsidi = a_subsidi.id_a_subsidi) as dipakai_sub_sy,
			(select COALESCE(sum(besar_subsidi),0) from b_sub_non_sy where id_a_subsidi = a_subsidi.id_a_subsidi) as dipakai_sub_non_sy,
			(total_subsidi-
				(select COALESCE(sum(besar_subsidi),0) from b_sub_sy where id_a_subsidi = a_subsidi.id_a_subsidi)-
				(select COALESCE(sum(besar_subsidi),0) from b_sub_non_sy where id_a_subsidi = a_subsidi.id_a_subsidi)
			) as sisa
		',false);
		$this->db->from('a_subsidi');
		$this->db->having('sisa >= 1');
		$this->db->having('id_a_subsidi',$id_a_subsidi);

		$query = $this->db->get();		
		return $query->result();
	}

	function tholabah_blm_disubsidi($id_a_non_sy )
	{
		$this->db->select('id_b_non_sy,b_non_sy.id_m_tholabah, nama_lengkap, alamat, jk');
		$this->db->from('b_non_sy');
		$this->db->join('m_tholabah','m_tholabah.id_m_tholabah = b_non_sy.id_m_tholabah');
		$this->db->where('id_a_non_sy',$id_a_non_sy );
		$this->db->where('b_non_sy.id_m_tholabah not in (
							select id_m_tholabah 
							from b_sub_non_sy 
							join b_non_sy on b_non_sy.id_b_non_sy = b_sub_non_sy.id_b_non_sy
							where id_a_non_sy = '.$id_a_non_sy.')'
		);
		$this->db->order_by('nama_lengkap');
		$query = $this->db->get();		
		return $query->result();
	}

	function save($dataInsert)
	{
		$query = $this->db->insert('b_sub_non_sy', $dataInsert);
		return $query;
	}

	function delete($id_b_sub_non_sy)
	{
		$this->db->where('id_b_sub_non_sy', $id_b_sub_non_sy);
		$delete = $this->db->delete('b_sub_non_sy');
		return $delete;
	}

}